unit uSQLs;

interface

const
  SQL_Users: string = 'SELECT US.ID,NAME,SURNAME, USERNAME FROM USERS US LEFT JOIN USER_HAS_PROFILE UHP ON UHP.USER_ID=US.ID WHERE US.ID<>1 ';
  SQL_Tables: string = 'select TA.ID, TA.NAME AS TABLE_NAME, TA.SEATS, (CASE WHEN CASH_GAME=1 THEN ''YES'' ELSE ''NO'' END) AS CASH_GAME, (CASE WHEN TA.DISABLED=1 THEN ''YES'' ELSE ''NO'' END) ' +
    ' AS DISABLED, tz.name AS TABLE_ZONE from tables ta LEFT join table_zones tz on tz.id=ta.table_zone_id ORDER BY TA.NAME';
  SQL_WinDist: string = 'select * from WIN_DISTRIBUTION ORDER BY NAME';
  SQL_WinPlayersNum: string = 'select ID, FROM_NUM, TO_NUM from WIN_PLAYERS_NUMBER where win_distribution_id=';
  SQL_WinPerc: string = 'SELECT ID, SEQ_INDEX, PERCENTAGE FROM WIN_PERCENTAGE WHERE WIN_PLAYERS_NUMBER_ID=';
  SQL_Profiles: string = 'select ID, NAME from PROFILES ORDER BY NAME';
  SQL_TournamentTypes: string = 'select ID, NAME from TOURNAMENT_TYPES ORDER BY NAME';
  SQL_Players: string = 'select ID, NAME, SURNAME,NICKNAME, BIRTH_DATE, STREET, CITY, (SELECT NAME FROM COUNTRIES WHERE ID=COUNTRY_ID) AS COUNTRY, EMAIL, CASE WHEN CAN_MAIL=1 THEN ''YES'' ELSE ''NO'' END AS CAN_MAIL from PLAYERS ORDER BY NAME';
  SQL_Tournaments: string = 'select TOU.ID,TO_CHAR(EVENT_START,''DD.MM.YY HH24:MI DY'') AS EVENT_START,TOU.NAME,  BUY_IN AS BUYIN,(FEE ||'' ''|| CASE WHEN FEE_IS_PERCENT=1' +
    ' THEN ''%'' WHEN FEE is null THEN '''' ELSE ''CURRENCY_SYMBOL'' END) AS FEE ,(RAKE ||'' ''|| CASE WHEN RAKE_IS_PERCENT=1' +
    ' THEN ''%'' WHEN RAKE is null THEN '''' ELSE ''CURRENCY_SYMBOL'' END) AS RAKE, STACK,(CASE WHEN REBUY_ALLOWED=0 THEN '''' ELSE ''YES'' END) AS REBUYS,' +
    ' ADDON,WD.NAME AS WIN_DISTRIBUTION, (SELECT NAME FROM TABLE_ZONES WHERE ID=DISPLAY_TABLE_ZONE_ID) AS DISPLAY_ZONE, LATE_REGISTRATION_MIN, TO_CHAR((SELECT ' +
    'EVENT_END FROM GETTOURNAMENT_START_END_TIME WHERE ID=TOU.ID),''HH24:MI'') AS EVENT_END from TOURNAMENTS TOU' +
    ' LEFT JOIN WIN_DISTRIBUTION WD ON TOU.WIN_DISTRIBUTION_ID=WD.ID WHERE TOU.DELETED=0 :DATA ORDER BY TOU.EVENT_START DESC';
  SQL_TournamentRounds: string =
    'SELECT ID, SEQ_INDEX, DURATION, SMALL_BLIND, BIG_BLIND,ANTE, CASE WHEN IS_BREAK=1 THEN ''BREAK'' ELSE '''' END AS BREAK, REBUY_OR_ADDON FROM TOURNAMENT_ROUNDS WHERE TOURNAMENT_ID=';
  SQL_Player_Notes: string = 'SELECT ID, DATETIME, NOTE  FROM PLAYER_NOTES WHERE PLAYER_ID=';
  SQL_Player_Picture: string = 'select IMAGE_SMALL, IMAGE_TYPE from PLAYERS where id=';
  SQL_User_Picture: string = 'select IMAGE, IMAGE_TYPE from USERS where id=';
  SQL_ReportTournamentsTickets: string = 'SELECT TICKET_ID, win_position, pay_value, NAME, BIRTH_DATE, COUNT_RE_BUY, datetime, to_char(out_datetime, ''hh24:mi:ss'') as out_datetime ' +
    ' FROM (select COUNT_RE_BUY,table_id,player_id,  TI.ID AS TICKET_ID,pl.name ||'' ''||pl.surname as name , pl.birth_date, ta.name as tble, ti.SEAT_NUM, ti.tournament_id, ti.datetime,' +
    ' ti.out_datetime, ti.win_position, ti.pay_value, TR_ID, TA_ID from tickets ti join players pl on ti.PLAYER_ID=pl.id' +
    ' LEFT JOIN (SELECT ID AS TR_ID, TICKET_ID FROM TICKET_REBUY WHERE ON_TABLE=1 AND VALUE IS NULL) TIR ON TIR.TICKET_ID=TI.ID' +
    ' LEFT JOIN (SELECT ID AS TA_ID, TICKET_ID FROM TICKET_ADDON WHERE ON_TABLE=1 AND VALUE IS NULL) TIA ON TIA.TICKET_ID=TI.ID' +
    ' left join  tables ta on ta.id=ti.table_id join (SELECT TOURNAMENT_ID, PLAYER_ID,' +
    ' (select count(0) from ticket_rebuy where VALUE IS not NULL and ticket_id=tix.id  and tournament_id=tix.tournament_id) AS COUNT_RE_BUY  FROM( select ti.id, TI.TOURNAMENT_ID, PL.ID as player_id from tickets ti join players pl on ti.PLAYER_ID=pl.id'
    + ' left join tables ta on ta.id=ti.table_id where deleted=0) tix GROUP BY TOURNAMENT_ID, PLAYER_ID, ID) x on (x.tournament_id =ti.tournament_id) and (pl.id=x.player_id)where ti.deleted=0)' +
    ' where ticket_id is not null and tournament_id=';
  SQL_TicketsGroupPlayers: string =
    'SELECT TICKET_ID, NAME,PLAYER_ID, BIRTH_DATE, TBLE, SEAT_NUM, COUNT_RE_BUY, COUNT_ADDON, datetime, to_char(out_datetime, ''hh24:mi:ss'') as out_datetime, win_position, pay_value, table_id, TR_ID AS DO_REBUY_ID, TA_ID AS DO_ADDON_ID'
    +
    ' ,PP FROM (select COUNT_ADDON, COUNT_RE_BUY,table_id,player_id,  TI.ID AS TICKET_ID,pl.name ||'' ''||pl.surname as name , pl.birth_date, ta.name as tble, ti.SEAT_NUM, ti.tournament_id, ti.datetime,'
    +
    ' ti.out_datetime, ti.win_position, ti.pay_value, TR_ID, TA_ID, (SELECT PP FROM GET_PP_TOURNAMENT_WIN_POS WHERE TICKET_ID=TI.ID) AS PP from tickets ti join players pl on ti.PLAYER_ID=pl.id'
    +
    ' LEFT JOIN (SELECT ID AS TR_ID, TICKET_ID FROM TICKET_REBUY WHERE ON_TABLE=1 AND VALUE IS NULL) TIR ON TIR.TICKET_ID=TI.ID' +
    ' LEFT JOIN (SELECT ID AS TA_ID, TICKET_ID FROM TICKET_ADDON WHERE ON_TABLE=1 AND VALUE IS NULL) TIA ON TIA.TICKET_ID=TI.ID'
    +
    ' left join  tables ta on ta.id=ti.table_id join (SELECT TOURNAMENT_ID, PLAYER_ID,' +
    ' (select count(0) from ticket_addon where VALUE IS not NULL and ticket_id=tix.id and tournament_id=tix.tournament_id) AS COUNT_ADDON, '
    + ' (select count(0) from ticket_rebuy where VALUE IS not NULL and ticket_id=tix.id and tournament_id=tix.tournament_id) AS COUNT_RE_BUY  FROM( select ti.id, TI.TOURNAMENT_ID, PL.ID as player_id from tickets ti join players pl on ti.PLAYER_ID=pl.id' + ' left join tables ta on ta.id=ti.table_id where deleted=0) tix GROUP BY TOURNAMENT_ID, PLAYER_ID, ID) x on (x.tournament_id =ti.tournament_id) and (pl.id=x.player_id)where ti.deleted=0)' + ' where ticket_id is not null and tournament_id=';
  SQL_TournamentTables: string = 'select tables.id, name from tournament_tables, tables where tournament_tables.table_id = tables.id and tournament_id = ';
  SQL_PresentTournamentData: string =
    'SELECT TOU.ID, EVENT_START, TOU.NAME, TT.NAME AS TOU_TYPE_NAME, REBUYS, ADDONS, PRIZEMONEY,TO_NUM,  TICKETS, PLAYING, PLAYERS_TICKETS, SEATS, TOTAL_STACK FROM(SELECT TOU.TOURNAMENT_TYPE_ID,' +
    ' TOU.ID, TOU.EVENT_START, TOU.NAME, REBUY_COUNT' + '  AS REBUYS,  ADDON_COUNT  AS ADDONS , (SELECT MONEY_PRIZE FROM GET_PRIZE_MONEY WHERE TOURNAMENT_ID=TOU.ID) AS PRIZEMONEY,' +
    ' COUNT(DISTINCT TI.PLAYER_ID) AS TICKETS, PLAYING, PLAYING||''/''||COUNT(DISTINCT TI.PLAYER_ID) AS PLAYERS_TICKETS, NVL(SEATS,0) AS SEATS, (SELECT TOTAL_CHIPS FROM GET_TOTAL_CHIPS WHERE TOURNAMENT_ID=TOU.ID) AS TOTAL_STACK' +
    ' FROM TOURNAMENTS TOU' +
    ' LEFT JOIN (SELECT TOURNAMENT_ID,SUM(SEATS) AS SEATS FROM TOURNAMENT_TABLES TT JOIN TABLES T ON T.ID=TT.TABLE_ID GROUP BY TOURNAMENT_ID) xr on xr.tournament_id=tou.id'
    + ' LEFT JOIN TICKETS TI ON TOU.ID=TI.TOURNAMENT_ID LEFT JOIN GET_PLAYERS_PLAYING_TICKETS GPPT ON GPPT.TOURNAMENT_ID=TOU.ID' +
    ' LEFT JOIN (SELECT COUNT(ID) AS REBUY_COUNT,SUM(VALUE) AS REBUY_VALUE, TOURNAMENT_ID FROM TICKET_REBUY WHERE VALUE IS NOT NULL AND TOURNAMENT_ID=:DATA GROUP BY TOURNAMENT_ID)TRX ON TRX.TOURNAMENT_ID=TOU.ID'
    + ' LEFT JOIN (SELECT COUNT(ID) AS ADDON_COUNT,SUM(VALUE) AS ADDON_VALUE, TOURNAMENT_ID FROM TICKET_ADDON WHERE VALUE IS NOT NULL AND TOURNAMENT_ID=:DATA GROUP BY TOURNAMENT_ID)TRY ON TRY.TOURNAMENT_ID=TOU.ID' +
    ' WHERE TI.DELETED=0 AND TOU.ID=:DATA '
    +
    ' GROUP BY TOU.ID, TOU.NAME, REBUY_ALLOWED, ADDON_ALLOWED, STACK, SEATS, EVENT_START, PLAYING,REBUY_COUNT,REBUY_VALUE ,ADDON_VALUE, ADDON_COUNT, TOU.TOURNAMENT_TYPE_ID ORDER BY EVENT_START DESC) TOU' +
    ' LEFT JOIN TOURNAMENT_TYPES TT ON TOU.TOURNAMENT_TYPE_ID=TT.ID' + ' LEFT JOIN GET_TOURNAMENT_WIN_TO_NUM GTW ON GTW.TOURNAMENT_ID=TOU.ID';
  SQL_GetWinValueFromPosition: string = 'SELECT PERCENTAGE/100*MONEY_PRIZE AS VALUE,TICKET_COUNT, TO_NUM FROM(SELECT WP.PERCENTAGE, TOU.BUY_IN, TICKET_COUNT ,MONEY_PRIZE,' +
    ' FROM_NUM, TO_NUM, SEQ_INDEX FROM WIN_DISTRIBUTION WD  left JOIN TOURNAMENTS TOU ON TOU.WIN_DISTRIBUTION_ID=WD.ID' +
    ' left join (SELECT MONEY_PRIZE, TICKET_COUNT, ID FROM GETTICKETTOURNAMENTS WHERE ID=:TOURNAMENT_ID) GTT on GTT.ID=TOU.ID' +
    ' LEFT JOIN WIN_PLAYERS_NUMBER WPN ON WPN.WIN_DISTRIBUTION_ID=WD.ID  JOIN WIN_PERCENTAGE WP ON WP.WIN_PLAYERS_NUMBER_ID=WPN.ID where tou.id=:TOURNAMENT_ID) WHERE SEQ_INDEX=:WIN_POS';
  SQL_IsRunningTournamentID: string = 'SELECT STOP_FROM FROM TOURNAMENT_STOPS TS where STOP_TO IS NULL ';
  SQL_GetTournamentIDForDateTime: string =
    'select id FROM tournaments tou where show_on_display=1 and deleted=0 and :DATA BETWEEN event_start AND (SELECT EVENT_END FROM GETTOURNAMENT_START_END_TIME WHERE ID=TOU.ID) ';
  SQL_CashGames: string =
    'SELECT CG.ID, STATUS, SMALL_BLIND||''/''|| BIG_BLIND AS BLINDS, CG.NAME, MIN_IN, (SELECT NAME FROM RAKES WHERE ID=RAKE_ID) AS RAKE, TA.NAME AS TBLE, TA.SEATS FROM CASH_GAMES CG LEFT JOIN TABLES TA ON TA.ID=CG.TABLE_ID order by small_blind asc';
  SQL_RoundTypes: string = 'SELECT * FROM TOURNAMENT_ROUND_TYPES';
  SQL_RoundTypeDetails: string =
    'SELECT ID, SEQ_INDEX, DURATION, SMALL_BLIND, BIG_BLIND,ANTE, CASE WHEN IS_BREAK=1 THEN ''BREAK'' ELSE '''' END AS BREAK, REBUY_OR_ADDON FROM TOURNAMENT_ROUND_TYPE_DETAILS WHERE ROUND_TYPE_ID=';
  SQL_Countries: string = 'SELECT ID,NAME FROM COUNTRIES ORDER BY NAME ASC';
  SQL_Drinks: string = 'SELECT ID,(SELECT NAME FROM DRINK_TYPES WHERE ID=DRINK_TYPE_ID) AS DRINK_TYPE,NAME, PRICE FROM DRINKS ORDER BY DRINK_TYPE ASC';
  SQL_CashGames_Players: string =
    'SELECT CGT.id, (SELECT PL.NAME||'' ''||PL.SURNAME FROM PLAYERS PL WHERE PL.ID=PLAYER_ID) AS PLAYER, SEAT_NUM, INPUT_VALUE,REBUY_VALUE, OUTPUT_VALUE, (OUTPUT_VALUE-INPUT_VALUE-NVL(REBUY_VALUE,0)) AS WIN, CGT.DATETIME,'
    + ' CASE WHEN CGTR.ACCEPT_DATETIME IS NULL AND CGTR.DATETIME IS NOT NULL THEN CGTR.ID END AS DO_REBUY_ID, PLAYER_ID, ACCEPT_DATETIME' +
    ' ,OUT_DATETIME, (SELECT PP FROM GET_PP_CASH_GAMES WHERE CASH_GAME_TICKET_ID=CGT.ID) AS POINTS FROM  CASH_GAME_TICKETS CGT LEFT JOIN (SELECT * FROM CASH_GAME_TICKET_REBUYS WHERE ACCEPT_DATETIME IS NULL AND '
    + 'ON_TABLE=1) CGTR ON CGT.ID=CGTR.CASH_GAME_TICKET_ID' +
    ' LEFT JOIN (SELECT SUM(VALUE) AS REBUY_VALUE, CASH_GAME_TICKET_ID FROM CASH_GAME_TICKET_REBUYS X1 WHERE ACCEPT_DATETIME IS NOT NULL GROUP BY CASH_GAME_TICKET_ID) CGTRV ON CGT.ID=CGTRV.CASH_GAME_TICKET_ID '
    + ' WHERE CGT.DELETED=0 AND ((CGT.DATETIME>=(select GD_START from GET_GD_START_TIME))OR OUT_DATETIME IS NULL) AND CGT.CASH_GAME_ID=';
  SQL_CageCashGame: string = 'SELECT * FROM GET_PRESENT_CASH_GAMES';
  SQL_TableZones: string = 'SELECT * FROM TABLE_ZONES ORDER BY NAME ASC';
  SQL_DrinkOrders: string =
    'SELECT DRO.ID,DRO.DATETIME, CASE WHEN TA.NAME IS NULL THEN ''BAR'' ELSE TA.NAME END AS TABLE_NAME, DRO.SEAT_NUM, PL.NAME||'' ''||PL.SURNAME AS PLAYER_NAME,DR.NAME as DRINK_NAME, COMPLETED, PRICE FROM DRINK_ORDERS DRO JOIN DRINKS DR ON'
    + ' DRO.DRINK_ID=DR.ID LEFT JOIN PLAYERS PL ON PL.ID=DRO.PLAYER_ID LEFT JOIN TABLES TA ON TA.ID=DRO.TABLE_ID ';
  SQL_Rakes: string = 'SELECT * FROM RAKES';
  SQL_RakeValues: string = 'SELECT ID, RAKE_ID, FROM_VALUE, TO_VALUE, VALUE, CASE WHEN IS_PERCENT=1 THEN ''%'' ELSE ''CURRENCY'' END AS IS_PERCENT FROM RAKE_VALUES';
  SQL_CasinoCashInventory: string = 'SELECT ID, DENOM_TYPE, DENOM_VALUE, NULL FROM CASINO_INVENTORY where is_chip=0 or is_chip is null order by DENOM_TYPE ASC, DENOM_VALUE DESC';
  SQL_CasinoChipInventory: string = 'SELECT ID, DENOM_TYPE, DENOM_VALUE, NULL FROM CASINO_INVENTORY where is_chip=1 order by DENOM_TYPE ASC, DENOM_VALUE DESC';
  SQL_CasinoInventory: string = 'SELECT ID, DENOM_TYPE, DENOM_VALUE, CASE WHEN IS_CHIP=1 THEN ''YES'' ELSE '''' END AS IS_CHIP FROM CASINO_INVENTORY order by DENOM_TYPE ASC, DENOM_VALUE DESC';
  SQL_DrinkTypes: string = 'SELECT * FROM DRINK_TYPES';
  SQL_LoginFormsProfiles: string = 'SELECT LF.ID,LF.NAME FROM LOGIN_FORM_PROFILES LFP JOIN LOGIN_FORMS LF ON LF.ID = LFP.LOGIN_FORM_ID WHERE LFP.PROFILE_ID=';

implementation

end.
